package com.mitac.mcd.cs.mobiletracking.socketserver.report;

import java.beans.PropertyVetoException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Calendar;

public class DailyDataMaker {

	/**
	 * 查询数据计算后生成exception日结统计数据然后存入db中
	 */
	public static void socketServerExceptionLogDailyReport(){
		java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;
    	ResultSet rs1 = null;
    	
    	Calendar date = Calendar.getInstance();
    	date.add(Calendar.DAY_OF_YEAR, -1);
    	int total_count = 0;
    	int year = date.get(Calendar.YEAR);
    	int month = date.get(Calendar.MONTH)+1;
    	int day = date.get(date.DAY_OF_MONTH);
    	
    	BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport: "+Formatter.db2_sdf.format(date.getTime())+"  exception日结开始...");
        String sqlA = " select EXCEPTION_PROP_CODE,"+
        				" count(E_LOG_ID) amount "+
        				" from EXCEPTION_LOG "+
        				" where year(RECORD_TIME) = "+year+" and month(RECORD_TIME) = "+month+" and day(RECORD_TIME) = "+day+
        				" group by EXCEPTION_PROP_CODE ";
        String sqlB = " select count(E_LOG_ID) amount from EXCEPTION_LOG "+
        				" where year(RECORD_TIME) = "+year+" and month(RECORD_TIME) = "+month+" and day(RECORD_TIME) = "+day;
        String sqlC = " insert into SOCKETSERVER_EXCEPTIONLOG_DAILYREPORT " +
        				" (EXCEPTION_PROP_CODE,COUNT,PERCENTOFTODAY,TOTAL_COUNT,LOG_TIME)" +
        				" values (?,?,?,?,?) ";
        String sqlD = " delete from SOCKETSERVER_EXCEPTIONLOG_DAILYREPORT " +
        				" where year(LOG_TIME) = "+year+" and month(LOG_TIME) = "+month+" and day(LOG_TIME) = "+day;
        try {
            dbc = DBConnection.getConnection("gpsdata");
            
            BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport:"+sqlD);
            pst = dbc.prepareStatement(sqlD);
            pst.executeUpdate();
            
            BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport:"+sqlA);
			pst = dbc.prepareStatement(sqlA);
			rs = pst.executeQuery();
			
			BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport:"+sqlB);
			pst = dbc.prepareStatement(sqlB);
			rs1 = pst.executeQuery();
			while(rs1.next()){
				total_count = rs1.getInt("amount");
			}
			
			BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport:"+sqlC);
			pst = dbc.prepareStatement(sqlC);
			DecimalFormat df2 = new DecimalFormat("##.00");
			while(rs.next()){
				String EXCEPTION_PROP_CODE = rs.getString("EXCEPTION_PROP_CODE");
				int count = rs.getInt("amount");
				double percentoftoday = total_count==0?0:Double.parseDouble(df2.format(count*100.0/total_count));
				BusinessUtil.aLogger.info("percentoftoday: "+percentoftoday);
				pst.setString(1, EXCEPTION_PROP_CODE);
				pst.setInt(2, count);
				pst.setDouble(3, percentoftoday);
				pst.setInt(4, total_count);
				pst.setTimestamp(5, new java.sql.Timestamp(date.getTime().getTime()));
				pst.addBatch();
			}
			pst.executeBatch();
			BusinessUtil.aLogger.info("socketServerExceptionLogDailyReport: "+Formatter.db2_sdf.format(date.getTime())+"  exception日结完成!");
			
			if(rs!=null) rs.close();
			if(rs1!=null) rs1.close();
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("socketServerExceptionLogDailyReport:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 查询数据计算后生成notification日结统计数据然后存入db中
	 */
	public static void socketServerNotificationDailyReport(){
		java.sql.Connection dbc=null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;
    	
    	Calendar date = Calendar.getInstance();
    	date.add(Calendar.DAY_OF_YEAR, -1);
    	int year = date.get(Calendar.YEAR);
    	int month = date.get(Calendar.MONTH)+1;
    	int day = date.get(date.DAY_OF_MONTH);
    	
    	BusinessUtil.aLogger.info("socketServerNotificationDailyReport: "+Formatter.db2_sdf.format(date.getTime())+"  notification日结开始...");
        String sqlA = " select count(distinct NL.NOTIFICATION_ID) receive_cmd_count,count(ND.UUID) receive_cmd_client_count,"+
        				" case when NL.NEWEST = 1 and NL.TYPE = 1 then count(distinct NL.NOTIFICATION_ID) end receive_newest_order_count, "+
        				" case when NL.NEWEST = 1 and NL.TYPE = 1 then count(ND.UUID) end receive_newest_order_client_count, "+
        				" case when NL.NEWEST = 1 and NL.TYPE = 0 then count(distinct NL.NOTIFICATION_ID) end receive_newest_msg_count, "+
        				" case when NL.NEWEST = 1 and NL.TYPE = 0 then count(ND.UUID) end receive_newest_msg_client_count, "+
        				" case when NL.NEWEST = 0 and NL.TYPE = 1 then count(distinct NL.NOTIFICATION_ID) end receive_unread_order_count, "+
        				" case when NL.NEWEST = 0 and NL.TYPE = 1 then count(ND.UUID) end receive_unread_order_client_count, "+
        				" case when NL.NEWEST = 0 and NL.TYPE = 0 then count(distinct NL.NOTIFICATION_ID) end receive_unread_msg_count, "+
        				" case when NL.NEWEST = 0 and NL.TYPE = 0 then count(ND.UUID) end receive_unread_msg_client_count, "+
        				" sum(case when ND.FAIL_CODE is NULL then 1 end) success_deliver_client, "+
        				" sum(case when ND.FAIL_CODE is not NULL then 1 end) fail_deliver_client "+
        				" from NOTIFICATION_LOG NL right join NOTIFICATION_DETAIL ND on NL.NOTIFICATION_ID = ND.NOTIFICATION_ID "+
        				" where year(NL.LOG_TIME) = "+year+" and month(NL.LOG_TIME) = "+month+" and day(NL.LOG_TIME) = "+day+
        				" and NL.IS_VALIDATE = 1 ";
        String sqlC = " insert into SOCKETSERVER_NOTIFICATION_DAILYREPORT " +
						" (" +
						" RECEIVE_CMD_COUNT,RECEIVE_CMD_CLIENT_COUNT," +
						" RECEIVE_NEWEST_ORDER_COUNT,RECEIVE_NEWEST_ORDER_CLIENT_COUNT," +
						" RECEIVE_NEWEST_MSG_COUNT,RECEIVE_NEWEST_MSG_CLIENT_COUNT," +
						" RECEIVE_UNREAD_ORDER_COUNT,RECEIVE_UNREAD_ORDER_CLIENT_COUNT," +
						" RECEIVE_UNREAD_MSG_COUNT,RECEIVE_UNREAD_MSG_CLIENT_COUNT," +
						" SUCCESS_DELIVER_CLIENT,FAIL_DELIVER_CLIENT," +
						" LOG_TIME" +
						" )" +
						" values (?,?,?,?,?,?,?,?,?,?,?,?,?) ";
        String sqlB = " delete from SOCKETSERVER_NOTIFICATION_DAILYREPORT " +
        				" where year(LOG_TIME) = "+year+" and month(LOG_TIME) = "+month+" and day(LOG_TIME) = "+day;
        try {
            dbc = DBConnection.getConnection("gpsdata");
            
            BusinessUtil.aLogger.info("socketServerNotificationDailyReport:"+sqlB);
            pst = dbc.prepareStatement(sqlB);
            pst.executeUpdate();
            
            BusinessUtil.aLogger.info("socketServerNotificationDailyReport:"+sqlA);
			pst = dbc.prepareStatement(sqlA);
			rs = pst.executeQuery();
			
			BusinessUtil.aLogger.info("socketServerNotificationDailyReport:"+sqlC);
			pst = dbc.prepareStatement(sqlC);
			while(rs.next()){
				int receive_cmd_count = rs.getInt("receive_cmd_count");
				int receive_cmd_client_count = rs.getInt("receive_cmd_client_count");
				int receive_newest_order_count = rs.getInt("receive_newest_order_count");
				int receive_newest_order_client_count = rs.getInt("receive_newest_order_client_count");
				int receive_newest_msg_count = rs.getInt("receive_newest_msg_count");
				int receive_newest_msg_client_count = rs.getInt("receive_newest_msg_client_count");
				int receive_unread_order_count = rs.getInt("receive_unread_order_count");
				int receive_unread_order_client_count = rs.getInt("receive_unread_order_client_count");
				int receive_unread_msg_count = rs.getInt("receive_unread_msg_count");
				int receive_unread_msg_client_count = rs.getInt("receive_unread_msg_client_count");
				int success_deliver_client = rs.getInt("success_deliver_client");
				int fail_deliver_client = rs.getInt("fail_deliver_client");
				pst.setInt(1, receive_cmd_count);
				pst.setInt(2, receive_cmd_client_count);
				pst.setInt(3, receive_newest_order_count);
				pst.setInt(4, receive_newest_order_client_count);
				pst.setInt(5, receive_newest_msg_count);
				pst.setInt(6, receive_newest_msg_client_count);
				pst.setInt(7, receive_unread_order_count);
				pst.setInt(8, receive_unread_order_client_count);
				pst.setInt(9, receive_unread_msg_count);
				pst.setInt(10, receive_unread_msg_client_count);
				pst.setInt(11, success_deliver_client);
				pst.setInt(12, fail_deliver_client);
				pst.setTimestamp(13, new java.sql.Timestamp(date.getTime().getTime()));
				
				pst.executeUpdate();
			}
			BusinessUtil.aLogger.info("socketServerNotificationDailyReport: "+Formatter.db2_sdf.format(date.getTime())+"  notification日结完成!");
			
			if(rs!=null) rs.close();
			dbc.close();
            pst.close();
		} catch (SQLException e) {
			BusinessUtil.aLogger.error("socketServerNotificationDailyReport:"+e);
		} catch (PropertyVetoException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String args[]){
		DecimalFormat df2 = new DecimalFormat("##.00");
		double percentoftoday = Double.parseDouble(df2.format(0*100.0/682));
		System.out.println(percentoftoday);
	}
}
